﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Mail_GetBriefInfo_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Mail_GetBriefInfo_V2];
GO
CREATE PROCEDURE [dbo].[sproc_Mail_GetBriefInfo_V2]
(
    @Username       nvarchar(50),
    @FolderType     int     
)
AS
BEGIN
SET NOCOUNT ON;

IF ( @FolderType <> /* external mail */ 4 )
BEGIN
    SELECT
        M.MailID,
        B.ClassID,
        (SELECT ClassName FROM dbo.UDS_Class C WHERE C.ClassID = B.ClassID) as ClassName,
        (SELECT RealName FROM dbo.UDS_Staff S where S.Staff_Name = B.Creator) as Sender,
        B.TimeCreated as MailSendDate,
        B.[Subject], 
        M.ReadFlag, 
        B.TOList,
        (SELECT COUNT(1) FROM dbo.UDS_MailAttachment A WHERE A.MailBodyID = B.MailBodyID) AS NumAttachFiles,
        (SELECT SUM(FileSize) FROM dbo.UDS_MailAttachment A WHERE A.MailBodyID = M.MailBodyID) AS AttachSize
    FROM
        dbo.UDS_Mail M
        INNER JOIN dbo.UDS_MailBody B ON M.MailBodyID = B.MailBodyID
    WHERE
        M.UserName = @UserName
        AND M.FolderType = @FolderType
    ORDER BY
        M.MailID DESC;
END

END
GO
